﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Collections;
using WF_QLThuVien.Utilities;
using WF_QLThuVien.Entities.PhieuMuon;
using WF_QLThuVien.Entities.Sach;

namespace WF_QLThuVien.DataLayer.PhieuMuon
{
    public class CPhieuMuonDAL : CConnection
    {
        OleDbCommand cmd = new OleDbCommand();
        public void KTConnect()
        {
            try
            {
                Connection();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        public ArrayList loadPhieuMuon()
        {
            KTConnect();
            try
            {
                ArrayList arrPhieuMuon = new ArrayList();
                cmd = new OleDbCommand("SELECT * FROM PhieuMuon", dataConnection);
                OleDbDataReader dataReader = cmd.ExecuteReader();
                if (dataReader == null)
                {
                    throw new Exception("Không có Phiếu Mượn - CPhieuMuonDAL");
                }
                else
                {
                    while (dataReader.Read())
                    {
                        CPhieuMuon pm = new CPhieuMuon();
                        
                        pm.PhieuMuonID = dataReader.GetValue(0).ToString();
                        pm.DocGiaID = dataReader.GetValue(1).ToString();
                        pm.NgayLap = Convert.ToDateTime(dataReader.GetValue(2));
                        List<CCTPhieuMuon> arrCTPM = new List<CCTPhieuMuon>();
                        cmd = new OleDbCommand("SELECT * FROM ChiTietPhieuMuon WHERE PhieuMuonID='" + pm.PhieuMuonID + "'", dataConnection);
                        OleDbDataReader reader = cmd.ExecuteReader();
                        while (reader.Read()) //Lấy ra ctpm + sách bên trong PM
                        {
                            CCTPhieuMuon ctpm = new CCTPhieuMuon();
                            CSach s=new CSach();
                            ctpm.PhieuMuonID = reader.GetValue(0).ToString();
                            #region: Tìm Sách
                            cmd = new OleDbCommand("SELECT * FROM Sach WHERE SachID='" + reader.GetValue(1).ToString() + "'", dataConnection);
                            OleDbDataReader readerSach = cmd.ExecuteReader();
                            while (readerSach.Read())
                            {
                                s = new CSach();
                                s.SachID = readerSach.GetValue(0).ToString();
                                s.DauSachID = readerSach.GetValue(1).ToString();
                                s.TrangThai = readerSach.GetValue(2).ToString();
                                s.GhiChu = readerSach.GetValue(3).ToString();
                            }
                            #endregion
                            ctpm.Sach = s;
                            ctpm.TrangThai = reader.GetValue(2).ToString();
                            ctpm.NgayTra = Convert.ToDateTime(reader.GetValue(3));
                            ctpm.SoLanGiaHan = Convert.ToInt32(reader.GetValue(4));
                            arrCTPM.Add(ctpm);
                        }
                        pm.CtPhieuMuon = arrCTPM;
                        //Chưa load CTPM
                        arrPhieuMuon.Add(pm);
                    }
                    return arrPhieuMuon;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Load Phiếu Mượn - CPhieuMuonDAL");
            }
        }

        public ArrayList LoadOnlyPMByMaDocGia(string maDocGia)
        {
            try
            {
                ArrayList arrPhieuMuon = new ArrayList();
                cmd = new OleDbCommand("SELECT * FROM PhieuMuon WHERE DocGiaID='" + maDocGia + "'", dataConnection);
                OleDbDataReader readerPM = cmd.ExecuteReader();
                if (readerPM == null)
                {
                    throw new Exception("Không Tìm Thấy!");
                }
                else
                {
                    while (readerPM.Read())
                    {
                        CPhieuMuon pm = new CPhieuMuon();
                        pm.PhieuMuonID = readerPM.GetValue(0).ToString();
                        pm.DocGiaID = readerPM.GetValue(1).ToString();
                        pm.NgayLap = Convert.ToDateTime(readerPM.GetValue(2));
                        List<CCTPhieuMuon> arrCTPM = new List<CCTPhieuMuon>();
                        cmd = new OleDbCommand("SELECT * FROM ChiTietPhieuMuon WHERE PhieuMuonID='" + pm.PhieuMuonID + "'", dataConnection);
                        OleDbDataReader reader = cmd.ExecuteReader();
                        while (reader.Read()) //Lấy ra ctpm + sách bên trong PM
                        {
                            CCTPhieuMuon ctpm = new CCTPhieuMuon();
                            CSach s = new CSach();
                            ctpm.PhieuMuonID = reader.GetValue(0).ToString();
                            #region: Tìm Sách
                            cmd = new OleDbCommand("SELECT * FROM Sach WHERE SachID='" + reader.GetValue(1).ToString() + "'", dataConnection);
                            OleDbDataReader readerSach = cmd.ExecuteReader();
                            while (readerSach.Read())
                            {
                                s = new CSach();
                                s.SachID = readerSach.GetValue(0).ToString();
                                s.DauSachID = readerSach.GetValue(1).ToString();
                                s.TrangThai = readerSach.GetValue(2).ToString();
                                s.GhiChu = readerSach.GetValue(3).ToString();
                            }
                            #endregion
                            ctpm.Sach = s;
                            ctpm.TrangThai = reader.GetValue(2).ToString();
                            ctpm.NgayTra = Convert.ToDateTime(reader.GetValue(3));
                            ctpm.SoLanGiaHan = Convert.ToInt32(reader.GetValue(4));
                            arrCTPM.Add(ctpm);
                        }
                        pm.CtPhieuMuon = arrCTPM;
                        //Chưa load CTPM
                        arrPhieuMuon.Add(pm);
                    }
                    return arrPhieuMuon;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Load Phiếu Mượn Từ Mã Đọc Giả - CPhieuMuonDAL\n" + ex.ToString());
            }
        }

        public ArrayList LoadPhieuMuonByMaSach(string maSach)
        {
            try
            {
                ArrayList arrPhieuMuon = new ArrayList();
                cmd = new OleDbCommand("SELECT * FROM ChiTietPhieuMuon WHERE SachID='" + maSach + "'", dataConnection);
                OleDbDataReader readerCTPM = cmd.ExecuteReader();
                if (readerCTPM == null)
                {
                    throw new Exception("Sai Mã Sách");
                }
                else
                {
                    CCTPhieuMuon ctpm = new CCTPhieuMuon();
                    List<CCTPhieuMuon> arrCTPM = new List<CCTPhieuMuon>();
                    while (readerCTPM.Read())
                    {
                        ctpm = new CCTPhieuMuon();
                        ctpm.PhieuMuonID = readerCTPM.GetValue(0).ToString();
                        CSach s = new CSach();
                        #region: Tìm Sách
                        cmd = new OleDbCommand("SELECT * FROM Sach WHERE SachID='" + readerCTPM.GetValue(1).ToString() + "'", dataConnection);
                        OleDbDataReader readerSach = cmd.ExecuteReader();
                        while (readerSach.Read())
                        {
                            s = new CSach();
                            s.SachID = readerSach.GetValue(0).ToString();
                            s.DauSachID = readerSach.GetValue(1).ToString();
                            s.TrangThai = readerSach.GetValue(2).ToString();
                            s.GhiChu = readerSach.GetValue(3).ToString();
                        }
                        #endregion
                        ctpm.Sach = s;
                        ctpm.TrangThai = readerCTPM.GetValue(2).ToString();
                        ctpm.NgayTra = Convert.ToDateTime(readerCTPM.GetValue(3).ToString());
                        ctpm.SoLanGiaHan = Convert.ToInt32(readerCTPM.GetValue(4).ToString());
                        arrCTPM.Add(ctpm);
                    }
                    CPhieuMuon pm = new CPhieuMuon();
                    //pm.CtPhieuMuon = arrCTPM;
                    cmd = new OleDbCommand("SELECT * FROM PhieuMuon WHERE PhieuMuonID='" + ctpm.PhieuMuonID + "'", dataConnection);
                    OleDbDataReader readerPM = cmd.ExecuteReader();
                    if (readerPM == null)
                    {
                        throw new Exception("Lỗi Load Phiếu Mượn Từ Mã Sách - CPhieuMuonDAL");
                    }
                    else
                    {
                        while (readerPM.Read())
                        {
                            pm=new CPhieuMuon();
                            pm.PhieuMuonID = readerPM.GetValue(0).ToString();
                            pm.DocGiaID = readerPM.GetValue(1).ToString();
                            pm.NgayLap = Convert.ToDateTime(readerPM.GetValue(2));
                            pm.CtPhieuMuon = arrCTPM;
                        }
                        arrPhieuMuon.Add(pm);
                    }
                    return arrPhieuMuon;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Load Phiếu Mượn Tự Mã Sách\n" + ex.ToString());
            }
        }

        public bool themPhieuMuon(CPhieuMuon pm)
        {
            KTConnect();
            try
            {
                //Kiểm tra có tồn tại mã đọc giả
                string docGiaID=pm.DocGiaID;
                cmd = new OleDbCommand("SELECT MaSoSV FROM DocGia WHERE DocGiaID='" + docGiaID + "'",dataConnection);
                string masoSV = (string)cmd.ExecuteScalar();
                if (masoSV == null)
                {
                    throw new Exception("Đọc Giả Không Tồn Tại");
                }
                else
                {
                    //Lập phiếu mượn
                    cmd = new OleDbCommand("INSERT INTO PhieuMuon VALUES('" + pm.PhieuMuonID + "', '" + pm.DocGiaID + "', #" + pm.NgayLap + "#)", dataConnection);
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi thêm Phiếu Mượn - CPhieuMuonDAL\n" + ex.ToString());
            }
        }

        public bool xoaPhieuMuon(CPhieuMuon pm)
        {
            try
            {
                cmd=new OleDbCommand("DELETE FROM PhieuMuon WHERE PhieuMuonID='"+pm.PhieuMuonID+"'",dataConnection);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi xóa Phiếu Mượn - CPhieuMuonDAL\n"+ex.ToString());
            }
        }

        #region: Tìm Kiếm
        public CPhieuMuon loadPhieuMuonByMaPhieuMaDocGia(string maPhieuMuon, string maDocGia)
        {
            KTConnect();
            try
            {
                CPhieuMuon pm = new CPhieuMuon();
                cmd = new OleDbCommand("SELECT * FROM PhieuMuon WHERE PhieuMuonID='" + maPhieuMuon + "' AND DocGiaID='" + maDocGia + "'", dataConnection);
                OleDbDataReader dataReader = cmd.ExecuteReader();
                if (dataReader == null)
                {
                    throw new Exception("Không Tìm Thấy Phiếu Mượn - CPhieuMuonDAL");
                }
                else
                {
                    while (dataReader.Read())
                    {
                        
                    }
                    return pm;
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Load Phiếu Mượn Khi Có Mã Phiếu + Mã Đọc Giả - CPhieuMuonDAL" + ex.ToString());
            }
        }
        #endregion
    }


}
